﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using OfficeOpenXml.Style;
using OfficeOpenXml;
using System.IO;
using System.Collections.Generic;
using System.Xml;
using System.Web;

namespace CRDWToolSet
{
    [ServiceContract(Namespace = "")]
    [SilverlightFaultBehavior]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class CRDWToolSetWebService
    {
        [OperationContract]
        public string DoWork()
        {
            // Add your operation implementation here
            return "Hello";
        }

        // Add more operations here and mark them with [OperationContract]
        [OperationContract]
        public byte[] DocumentBiarToExcel(string BiarFileName, byte[] BiarFileData, string FileProperties)
        {
            byte[] outputFileData = new byte[0];

            try
            {
                string tempWorkFolder = Path.Combine(HttpContext.Current.Server.MapPath("Temp"), Guid.NewGuid().ToString());
                if (Directory.Exists(tempWorkFolder))
                {
                    Directory.Delete(tempWorkFolder);
                }

                Directory.CreateDirectory(tempWorkFolder);

                BiarFileName = Path.Combine(tempWorkFolder, BiarFileName);

                if (File.Exists(BiarFileName))
                {
                    File.Delete(BiarFileName);
                }

                using (FileStream fs = new FileStream(BiarFileName, FileMode.Create))
                {
                    fs.Write(BiarFileData, 0, (int)BiarFileData.Length);
                }
                if (File.Exists(BiarFileName))
                {
                    ICSharpCode.SharpZipLib.Zip.FastZip unzip = new ICSharpCode.SharpZipLib.Zip.FastZip();
                    unzip.ExtractZip(BiarFileName, tempWorkFolder, "businessobjects.xml");
                    string settledFileName = Path.Combine(tempWorkFolder, "businessobjects.xml");
                    if (File.Exists(settledFileName))
                    {

                        Dictionary<string, string> filePropertiesDict = new Dictionary<string, string>();
                        filePropertiesDict.Add("File Name",Path.GetFileName(BiarFileName).Replace("zip","biar"));
                        //filePropertiesDict.Add("File Size(K)", FileProperties.Split('|')[1]);
                        //filePropertiesDict.Add("MD5 Checksum", FileProperties.Split('|')[2]);
                        //filePropertiesDict.Add("Last Modification",FileProperties.Split('|')[3]);

                        #region XML Name Space
                        XmlDocument doc = new XmlDocument();
                        doc.Load(settledFileName);

                        XmlNamespaceManager nsmgr = new XmlNamespaceManager(new XmlDocument().NameTable);
                        foreach (XmlAttribute attribute in doc.DocumentElement.Attributes)
                        {
                            if (attribute.Name.StartsWith("xmlns"))
                            {
                                nsmgr.AddNamespace(attribute.Name.Split(':')[1], attribute.Value);
                            }
                        }
                        #endregion

                        #region InfoObject Type Dictionary
                        SortedDictionary<string, int> objectTypeDict = new SortedDictionary<string, int>();

                        foreach (XmlNode oneNode in doc.DocumentElement.ChildNodes)
                        {
                            string objectType = (oneNode.Attributes.GetNamedItem("xsi:type").Value);
                            int val = 0;
                            if (objectTypeDict.TryGetValue(objectType, out val))
                            {
                                objectTypeDict[objectType] = val + 1;
                            }
                            else
                            {
                                objectTypeDict.Add(objectType, 1);
                            }
                        }

                        #endregion

                        #region Generate Excel Document

                        string outputExcelFileName = Path.Combine(tempWorkFolder, BiarFileName.Replace("zip", "xlsx"));// @"\BIARVerify" + DateTime.Now.ToString("MMddHHmmss") + @".xlsx"; //;

                        FileInfo newFile = new FileInfo(outputExcelFileName);
                        if (newFile.Exists)
                        {
                            newFile.Delete();
                            newFile = new FileInfo(outputExcelFileName);
                        }
                        using (ExcelPackage xlPackage = new ExcelPackage(newFile))
                        {
                            int row = 2;
                            int column = 2;
                            ExcelWorksheet worksheet;
                            string objectTypeName = "";
                            ExcelRange range;

                            #region Summary Sheet
                            worksheet = xlPackage.Workbook.Worksheets.Add("Summary");

                            range = worksheet.Cells[1, 1, 1, 2];
                            range.Merge = true;
                            range.Value = "BIAR File Properties";
                            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
                            range.Style.Font.Color.SetColor(System.Drawing.Color.White);

                            foreach (KeyValuePair<string, string> item in filePropertiesDict)
                            {
                                worksheet.Cells[row, 1].Value = item.Key;
                                if (item.Key == "File Size(K)")
                                {
                                    worksheet.Cells[row, 2].Value = Convert.ToInt32(item.Value);
                                }
                                else
                                {
                                    worksheet.Cells[row, 2].Value = item.Value;
                                }
                                row++;

                            }

                            range = worksheet.Cells[2, 1, row - 1, 2];

                            range.Style.Border.Top.Style = ExcelBorderStyle.Hair;
                            range.Style.Border.Bottom.Style = ExcelBorderStyle.Hair;
                            range.Style.Border.Left.Style = ExcelBorderStyle.Hair;
                            range.Style.Border.Right.Style = ExcelBorderStyle.Hair;

                            range.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                            range.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                            range.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                            range.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);

                            row++;
                            int pkgInfoStartRow = row;
                            range = worksheet.Cells[row, 1, row, 2];
                            range.Merge = true;
                            range.Value = "BIAR Summary Information";
                            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                            row++;

                            worksheet.Cells[row, 1].Value = "Object Type";
                            worksheet.Cells[row, 2].Value = "Count";

                            range = worksheet.Cells[row - 1, 1, row, 2];
                            range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
                            range.Style.Font.Color.SetColor(System.Drawing.Color.White);

                            row++;

                            foreach (KeyValuePair<string, int> item in objectTypeDict)
                            {
                                objectTypeName = item.Key.Split(':')[1];
                                worksheet.Cells[row, 1].Value = objectTypeName;
                                worksheet.Cells[row, 2].Value = item.Value;
                                worksheet.Cells[row, 1].Hyperlink = new ExcelHyperLink(objectTypeName + @"!A1", objectTypeName); ;
                                row++;

                            }
                            worksheet.Cells[row, 1].Value = "Total";
                            worksheet.Cells[row, 2].Formula = "sum(B2:B" + (row - 1).ToString() + ")";

                            range = worksheet.Cells[row, 1, row, 2];
                            range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
                            range.Style.Font.Color.SetColor(System.Drawing.Color.White);

                            range = worksheet.Cells[pkgInfoStartRow + 2, 1, row - 1, 1];
                            range.Style.Font.UnderLine = true;
                            range.Style.Font.Color.SetColor(System.Drawing.Color.Blue);

                            range = worksheet.Cells[pkgInfoStartRow, 1, row - 1, 2];

                            range.Style.Border.Top.Style = ExcelBorderStyle.Hair;
                            range.Style.Border.Bottom.Style = ExcelBorderStyle.Hair;
                            range.Style.Border.Left.Style = ExcelBorderStyle.Hair;
                            range.Style.Border.Right.Style = ExcelBorderStyle.Hair;

                            range.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                            range.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                            range.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                            range.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);

                            worksheet.Column(1).AutoFit();
                            worksheet.Column(2).AutoFit();
                            #endregion

                            #region Detail Sheet
                            foreach (KeyValuePair<string, int> item in objectTypeDict)
                            {
                                worksheet = xlPackage.Workbook.Worksheets.Add(item.Key.Split(':')[1]);
                                row = 2;
                                column = 1;

                                objectTypeName = item.Key.Split(':')[1];

                                string parentCuid;
                                XmlNode parentNode;

                                switch (objectTypeName)
                                {
                                    case "CrystalReport":
                                        #region Crystal Report

                                        worksheet.Cells[1, 1].Value = "Report Name";
                                        worksheet.Cells[1, 2].Value = "Report ID";
                                        worksheet.Cells[1, 3].Value = "Parent Folder";
                                        foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                        {
                                            //report name
                                            worksheet.Cells[row, 1].Value = node.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            //report id
                                            try { worksheet.Cells[row, 2].Value = node.SelectSingleNode("io:Description", nsmgr).InnerText.Split('|')[0].Split(':')[1].Trim(); }
                                            catch { worksheet.Cells[row, 2].Value = ""; }
                                            //parent folder
                                            parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                            parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                            worksheet.Cells[row, 3].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            row++;
                                        }
                                        column = 3;
                                        #endregion
                                        break;
                                    case "Webi":
                                        #region Webi Report

                                        worksheet.Cells[1, 1].Value = "Report Name";
                                        worksheet.Cells[1, 2].Value = "Report ID";
                                        worksheet.Cells[1, 3].Value = "Parent Folder";
                                        foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                        {
                                            //report name
                                            worksheet.Cells[row, 1].Value = node.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            //report id
                                            try { worksheet.Cells[row, 2].Value = node.SelectSingleNode("io:Description", nsmgr).InnerText.Split('|')[0].Split(':')[1].Trim(); }
                                            catch { worksheet.Cells[row, 2].Value = ""; }
                                            //parent folder
                                            parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                            parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                            worksheet.Cells[row, 3].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            row++;
                                        }
                                        column = 3;
                                        #endregion
                                        break;
                                    case "Folder":
                                        #region Folder
                                        worksheet.Cells[1, 1].Value = "Folder Name";
                                        worksheet.Cells[1, 2].Value = "Parent Folder Name";
                                        foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                        {
                                            worksheet.Cells[row, 1].Value = node.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            //parent folder
                                            try
                                            {
                                                parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                                parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                                worksheet.Cells[row, 2].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            }
                                            catch (NullReferenceException)
                                            {
                                                worksheet.Cells[row, 2].Value = "";
                                            }

                                            row++;
                                        }
                                        column = 2;
                                        #endregion
                                        break;
                                    case "MetaData.DataDBField":
                                        #region MetaData.DataDBField
                                        worksheet.Cells[1, 1].Value = "Field Name";
                                        worksheet.Cells[1, 2].Value = "Parent Table";
                                        foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                        {
                                            //report name
                                            worksheet.Cells[row, 1].Value = node.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            //Parent Table
                                            parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                            parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                            worksheet.Cells[row, 2].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            row++;
                                        }
                                        column = 2;
                                        #endregion
                                        break;
                                    case "MetaData.DataCommandTable":
                                        #region MetaData.DataCommandTable
                                        worksheet.Cells[1, 1].Value = "Command Table Name";
                                        worksheet.Cells[1, 2].Value = "Parent Name";
                                        worksheet.Cells[1, 3].Value = "Command Text";
                                        foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                        {
                                            //command table name
                                            worksheet.Cells[row, 1].Value = node.SelectNodes("io:Name", nsmgr)[0].InnerText;
                                            //Parent Table
                                            parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                            parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                            worksheet.Cells[row, 2].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            //command text
                                            worksheet.Cells[row, 3].Value = node.SelectSingleNode("md.dct:MetadataProperties", nsmgr).SelectSingleNode("md.dct:CommandText", nsmgr).InnerText;
                                            row++;
                                        }
                                        column = 3;
                                        #endregion
                                        break;
                                    case "MetaData.BusinessFormulaField":
                                        #region MetaData.BusinessFormulaField
                                        worksheet.Cells[1, 1].Value = "Formula Field Name";
                                        worksheet.Cells[1, 2].Value = "Parent Table";
                                        worksheet.Cells[1, 3].Value = "Formula Text";
                                        foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                        {
                                            //report name
                                            worksheet.Cells[row, 1].Value = node.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            //Parent Table
                                            parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                            parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                            worksheet.Cells[row, 2].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            //Formula Text
                                            worksheet.Cells[row, 3].Value = node.SelectSingleNode("md.ff:MetadataProperties", nsmgr).SelectSingleNode("md.ff:FormulaText", nsmgr).InnerText;
                                            row++;
                                        }
                                        column = 3;
                                        #endregion
                                        break;
                                    default:
                                        #region Others
                                        worksheet.Cells[1, 1].Value = "Object Name";
                                        worksheet.Cells[1, 2].Value = "Parent Object Name";
                                        foreach (XmlNode node in doc.SelectNodes("/io:InfoObjects/io:InfoObject[@xsi:type='" + item.Key + "']", nsmgr))
                                        {
                                            worksheet.Cells[row, 1].Value = node.SelectNodes("io:Name", nsmgr)[0].InnerText;
                                            //parent object 
                                            try
                                            {
                                                parentCuid = node.SelectSingleNode("io:ParentCuid", nsmgr).InnerText;
                                                parentNode = doc.DocumentElement.SelectSingleNode("/io:InfoObjects/io:InfoObject[io:Cuid='" + parentCuid + "']", nsmgr);
                                                worksheet.Cells[row, 2].Value = parentNode.SelectSingleNode("io:Name", nsmgr).InnerText;
                                            }
                                            catch (NullReferenceException)
                                            {
                                                worksheet.Cells[row, 2].Value = "";
                                            }
                                            row++;
                                        }
                                        column = 2;
                                        #endregion
                                        break;
                                }


                                #region Adjust column Width
                                for (int i = 1; i <= column; i++)
                                {
                                    worksheet.Column(i).AutoFit();
                                }
                                #endregion

                                #region Column Header Font and Background
                                range = worksheet.Cells[1, 1, 1, column];
                                range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
                                range.Style.Font.Color.SetColor(System.Drawing.Color.White);
                                #endregion

                                #region Cell Border
                                range = worksheet.Cells[2, 1, row - 1, column];
                                range.Style.Border.Top.Style = ExcelBorderStyle.Hair;
                                range.Style.Border.Bottom.Style = ExcelBorderStyle.Hair;
                                range.Style.Border.Left.Style = ExcelBorderStyle.Hair;
                                range.Style.Border.Right.Style = ExcelBorderStyle.Hair;

                                range.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                                range.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                                range.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                                range.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
                                #endregion
                            }
                            #endregion

                            xlPackage.Save();

                        }

                        #endregion
                        using (FileStream fs = newFile.OpenRead())
                        {
                            outputFileData = new byte[fs.Length];

                            fs.Read(outputFileData, 0, outputFileData.Length);
                        }

                    }
                }
                //if (Directory.Exists(tempWorkFolder))
                //{
                //    Directory.Delete(tempWorkFolder);
                //}
            }
            catch (Exception)
            {

                outputFileData = new byte[0];
            }

            return outputFileData;
        }

    }
}
